import traceback
import pyautogui
import os
import time
import pyperclip as clip
import json
import pandas as pd
import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.configUtil import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.mouseMoveUtil import *

runMode = '月模式'  # 日、周、月 三种模式都有的
# 拼多多-商家后台-商品数据-商品概况
modeName = '拼多多_商家后台_商品概况'


# 左边点击，直到点击到一级类目，该过程不参与循环
def SPGK(engine, conn):
    # 关闭电霸每天晚上会弹出的一个屏蔽页面
    pyautogui.FAILSAFE = False

    sleep(6)
    # 左边--【物流工具】
    viaCommonUrl('https://mms.pinduoduo.com/orders/order/carriage/list')

    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    # 左边--【商品数据】
    viaCommonUrl('https://mms.pinduoduo.com/sycm/goods_effect')

    jsonFromWebShopName = getSjhtShopName()

    # ==============F12的filter过滤框
    pyautogui.moveTo(1571, 167, 6)
    pyautogui.doubleClick()

    # 过滤【实时数据】
    clip.copy('queryGoodsPageRT')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    # 选中过滤文件
    pyautogui.moveTo(1540, 325, 6)
    pyautogui.click()

    # 点中json值区域
    pyautogui.moveTo(1812, 497, 6)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')
    # bug fix
    pyautogui.moveTo(1571, 167, 2)

    # 读取剪切板内容queryGoodsPageRT
    sleep(2)
    value = clip.paste()
    jsonFromWeb1 = json.loads(value)

    # ==============F12的filter过滤框
    # clear network
    pyautogui.moveTo(1541, 143, 6)
    pyautogui.click()

    if runMode == '日模式':
        pyautogui.moveTo(1180, 532, 6)
        pyautogui.click()

    if runMode == '周模式':
        pyautogui.moveTo(1238, 532, 6)
        pyautogui.click()

    if runMode == '月模式':
        pyautogui.moveTo(1307, 532, 6)
        pyautogui.click()

    pyautogui.moveTo(1571, 167, 6)
    pyautogui.doubleClick()

    # 过滤
    clip.copy('queryGoodsPageOverView')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    # 选中过滤文件（第二个文件才是对的）   第二个:348    第一个:326
    pyautogui.moveTo(1539, 326, 6)
    pyautogui.click()

    # 点中json值区域
    pyautogui.moveTo(1812, 497, 6)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容
    sleep(2)
    value = clip.paste()
    jsonFromWeb2 = json.loads(value)

    jsonDf = {'payOrdrAmt': [jsonFromWeb1['result']['payOrdrAmt']],
              'payOrdrCnt': [jsonFromWeb1['result']['payOrdrCnt']],
              'payUvRto': [jsonFromWeb1['result']['payUvRto']],
              'gpv': [jsonFromWeb1['result']['gpv']],
              'guv': [jsonFromWeb1['result']['guv']],
              'vstGoodsCnt': [jsonFromWeb1['result']['vstGoodsCnt']],
              'payOrdrAmtRt': [jsonFromWeb1['result']['payOrdrAmtRt']],
              'payOrdrCntRt': [jsonFromWeb1['result']['payOrdrCntRt']],
              'payUvRtoRt': [jsonFromWeb1['result']['payUvRtoRt']],
              'gpvRt': [jsonFromWeb1['result']['gpvRt']],
              'guvRt': [jsonFromWeb1['result']['guvRt']],
              'vstGoodsCntRt': [jsonFromWeb1['result']['vstGoodsCntRt']],
              'newMerchant': [jsonFromWeb1['result']['payOrdrAmt']],
              "payOrdrAmt_Agg": [jsonFromWeb2['result']['payOrdrAmt']],
              "payOrdrAmtPct_Agg": [jsonFromWeb2['result']['payOrdrAmtPct']],
              "payOrdrAmtIsPercent_Agg": [jsonFromWeb2['result']['payOrdrAmtIsPercent']],
              "payOrdrCnt_Agg": [jsonFromWeb2['result']['payOrdrCnt']],
              "payOrdrCntPct_Agg": [jsonFromWeb2['result']['payOrdrCntPct']],
              "payOrdrCntIsPercent_Agg": [jsonFromWeb2['result']['payOrdrCntIsPercent']],
              "payUvRto_Agg": [jsonFromWeb2['result']['payUvRto']],
              "payUvRtoPct_Agg": [jsonFromWeb2['result']['payUvRtoPct']],
              "payUvRtoIsPercent_Agg": [jsonFromWeb2['result']['payUvRtoIsPercent']],
              "gpv_Agg": [jsonFromWeb2['result']['gpv']],
              "gpvPct_Agg": [jsonFromWeb2['result']['gpvPct']],
              "gpvIsPercent_Agg": [jsonFromWeb2['result']['gpvIsPercent']],
              "guv_Agg": [jsonFromWeb2['result']['guv']],
              "guvPct_Agg": [jsonFromWeb2['result']['guvPct']],
              "guvIsPercent_Agg": [jsonFromWeb2['result']['guvIsPercent']],
              "vstGoodsCnt_Agg": [jsonFromWeb2['result']['vstGoodsCnt']],
              "vstGoodsCntPct_Agg": [jsonFromWeb2['result']['vstGoodsCntPct']],
              "vstGoodsCntIsPercent_Agg": [jsonFromWeb2['result']['vstGoodsCntIsPercent']],
              "payOrdrUsrCnt_Agg": [jsonFromWeb2['result']['payOrdrUsrCnt']],
              "payOrdrUsrCntPct_Agg": [jsonFromWeb2['result']['payOrdrUsrCntPct']],
              "payOrdrUsrCntIsPercent_Agg": [jsonFromWeb2['result']['payOrdrUsrCntIsPercent']],
              "cfmOrdrUsrCnt_Agg": [jsonFromWeb2['result']['cfmOrdrUsrCnt']],
              "cfmOrdrUsrCntPct_Agg": [jsonFromWeb2['result']['cfmOrdrUsrCntPct']],
              "cfmOrdrUsrCntIsPercent_Agg": [jsonFromWeb2['result']['cfmOrdrUsrCntIsPercent']],
              "cfmOrdrCnt_Agg": [jsonFromWeb2['result']['cfmOrdrCnt']],
              "cfmOrdrCntPct_Agg": [jsonFromWeb2['result']['cfmOrdrCntPct']],
              "cfmOrdrCntIsPercent_Agg": [jsonFromWeb2['result']['cfmOrdrCntIsPercent']],
              "goodsFavCnt_Agg": [jsonFromWeb2['result']['goodsFavCnt']],
              "goodsFavCntPct_Agg": [jsonFromWeb2['result']['goodsFavCntPct']],
              "goodsFavCntIsPercent_Agg": [jsonFromWeb2['result']['goodsFavCntIsPercent']]
              }
    df = pd.read_json(json.dumps(jsonDf))
    df['statDate'] = datetime.datetime.now()

    df['shopName'] = jsonFromWebShopName['result']['username']

    df['运行模式'] = runMode
    df['统计起始日'] = ''
    df['统计结束日'] = ''
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    df['插入时间'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-30)).strftime(
            "%Y-%m-%d")
        df['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                      datetime.datetime.now().day)
        df['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-7)).strftime(
            "%Y-%m-%d")
        df['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['statDate'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日周月'] = df['统计日']
    # 解决可能出现的超时问题bugfix 2020.08.18
    conn.connection.connection.ping(reconnect=True)
    df.to_sql(name='拼多多_商家后台_商品概况', con=conn, if_exists='append', index=False)

    resetGuiAtLastStepCloseF12ToTop()

    return

def deleteDuplicationDataDSR(engine):
    # 这里有特殊的逻辑，不同账号，可能有相同的类目，同一天的数据，是一模一样的，因此我们需要去除重复数据的
    # =====================================================================================================
    # 拼多多_商家后台_商品概况
    sql = 'delete from a                                                              '
    sql += '    using 拼多多_商家后台_商品概况 as a, 拼多多_商家后台_商品概况 as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.statDate = b.statDate and a.运行模式=b.运行模式 and a.shopName=b.shopName and a.统计起始日=b.统计起始日 and a.统计结束日=b.统计结束日 and a.运行模式=b.运行模式)     '
    engine.execute(sql)

def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine('拼多多_商家后台_商品概况', engine, conn, '', runMode)
        SPGK(engine, conn)
        deleteDuplicationDataDSR(engine)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
